/*
 *************************************************************************
 *西瑞克斯研发管理系统
 *版 本: 1.0   
 *日 期：02/08/2012    
 *作 者： 王浩 QQ：873987060   
 *************************************************************************   
 */
package com.application.usertable.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.application.role.entity.Role;
import com.application.systemmanager.struts.form.SystemHelpForm;
import com.application.usertable.entity.RoleUser;
import com.application.usertable.entity.Usertable;
import com.application.usertable.struts.form.UsertableForm;
import com.manager.BaseDao;
import com.manager.commons.DateUtil;
import com.manager.commons.page.PageBean;

/**
 * 
 * Description:UsertableDAO<br />
 * 
 * Version:V0.1<br />
 * 
 * Author: 左磊 QQ：30575951 <br />
 * 
 * History: <br />
 * 
 * 02/08/2012
 * 
 */
public class UsertableDAO extends BaseDao {
	private static final Logger logger = Logger.getLogger(UsertableDAO.class);

	private Usertable usertable;

	public Usertable getUsertable() {
		return this.usertable;
	}

	public void setUsertable(Usertable usertable) {
		this.usertable = usertable;
	}

	public Integer getAllCountUsertable(UsertableForm usertableForm) {
		// 储存查询条件的buffer
		StringBuffer buf = new StringBuffer();
		buf.append("select count(*) from Usertable t ");
		// 查询字符串
		String strSql = getStringBuffer(usertableForm, buf).toString();

		return Integer.parseInt(this.getHibernateTemplate().find(strSql).get(0)
				.toString());
	}

	public void deleteUsertables(UsertableForm usertableForm) {
		String a = usertableForm.getIds().replaceAll(",", "','");
		String hql = " delete from Usertable where id in  ( '" + a + "')";
		Session session = this.getSession();
		try {
			session.createQuery(hql).executeUpdate();
		} catch (RuntimeException re) {
			logger.error("deleteUsertables failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
	}

	public List searchUsertable(final UsertableForm usertableForm,
			final PageBean pageBean) {
		StringBuffer buf = new StringBuffer();

		// 返回list集合
		List list = null;

		buf.append("from Usertable t  ");

		// 查询字符串条件
		buf = getStringBuffer(usertableForm, buf);

		// 传递查询条件buf值
		pageBean.setStringBuffer(buf);

		// 分页取得字符串集合
		list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List resutlList = new ArrayList();
				try {
					Query query = session.createQuery(pageBean
							.getStringBuffer().toString()
							+ " order by t.createDate Desc ");

					// 设置开始页
					query.setFirstResult(pageBean.getPageSize()
							* (pageBean.getPageIndex() - 1));

					// 设置结束页
					query.setMaxResults(pageBean.getPageSize());

					List teamsList = query.list();
					if (teamsList != null && teamsList.size() > 0) {
						for (int j = 0; j < teamsList.size(); j++) {
							Usertable usertable = (Usertable) teamsList.get(j);
							List<Role> role = getUserRole(usertable.getId());
							String roles = "";
							for (int i = 0; i < role.size(); i++) {
								roles = role.get(i).getName() + " " + roles;
							}
							usertable.setRole(roles);
							resutlList.add(usertable);
						}
					}
					return resutlList;
				} catch (RuntimeException re) {
					logger.error("searchUsertable failed");
					throw re;
				} finally {
					if (session != null) {
						releaseSession(session);
					}
				}
			}
		});
		return list;
	}

	public List searchUsertable(final UsertableForm usertableForm) {
		StringBuffer buf = new StringBuffer();

		// 返回list集合
		List list = null;

		buf.append("from Usertable t  ");

		// 查询字符串条件
		buf = getStringBuffer(usertableForm, buf);
		final String sql = buf.toString();

		// 分页取得字符串集合
		list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List resutlList = new ArrayList();
				try {
				Query query = session.createQuery(sql
						+ " order by t.createDate Desc ");
				
				List teamsList = query.list();
				if (teamsList != null && teamsList.size() > 0) {
					for (int j = 0; j < teamsList.size(); j++) {
						Usertable usertable = (Usertable) teamsList.get(j);
						List<Role> role = getUserRole(usertable.getId());
						String roles = "";
						for (int i = 0; i < role.size(); i++) {
							roles = role.get(i).getName() + " " + roles;
						}
						usertable.setRole(roles);
						resutlList.add(usertable);
					}
				}
				return resutlList;
				} catch (RuntimeException re) {
					logger.error("searchUsertable failed");
					throw re;
				} finally {
					if (session != null) {
						releaseSession(session);
					}
				}
			}
		});
		return list;
	}

	public void deleteUserRole(String uid) {
		String hql = " delete from RoleUser where uid = '" + uid + "'";
		Session session = this.getSession();
		try {
			session.createQuery(hql).executeUpdate();
		} catch (RuntimeException re) {
			logger.error("deleteUserRole failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
	}

	public List<Role> getUserRole(String uid) {
		List<Role> roleTemp = new ArrayList();
		String hql = "from RoleUser ru,Role r where r.id = ru.rid and ru.uid = '"
				+ uid + "'";
		Session session = this.getSession();
		try {
			List<Object[]> temp = session.createQuery(hql).list();
			if (temp != null && temp.size() > 0) {
				for (int i = 0; i < temp.size(); i++) {
					Object[] obj = temp.get(i);
					Role role = (Role) obj[1];
					roleTemp.add(role);
				}
			}
		} catch (RuntimeException re) {
			logger.error("getUserRole failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
		return roleTemp;
	}

	public List<Usertable> getUserById(String rid) {
		List<Usertable> roleTemp = new ArrayList();
		String hql = "from Usertable u,RoleUser ru where ru.uid=u.id and ru.rid='"
				+ rid + "' and u.username<>'sys'";
		Session session = this.getSession();
		try {
			List<Object[]> temp = session.createQuery(hql).list();
			if (temp != null && temp.size() > 0) {
				for (int i = 0; i < temp.size(); i++) {
					Object[] obj = temp.get(i);
					Usertable role = (Usertable) obj[0];
					roleTemp.add(role);
				}
			}
		} catch (RuntimeException re) {
			logger.error("getUserRole failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
		return roleTemp;
	}

	public List<Usertable> getUserById(String rid, String oid) {
		List<Usertable> roleTemp = new ArrayList();
		String hql = "from Usertable u,RoleUser ru where ru.uid=u.id and ru.rid='"
				+ rid + "' and u.oid = '" + oid + "' and u.username<>'sys'";
		Session session = this.getSession();
		try {
			List<Object[]> temp = session.createQuery(hql).list();
			if (temp != null && temp.size() > 0) {
				for (int i = 0; i < temp.size(); i++) {
					Object[] obj = temp.get(i);
					Usertable role = (Usertable) obj[0];
					roleTemp.add(role);
				}
			}
		} catch (RuntimeException re) {
			logger.error("getUserRole failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
		return roleTemp;
	}

	public List<Usertable> getUserByIdBm(String oid) {
		List<Usertable> roleTemp = new ArrayList();
		String hql = "from Usertable u where u.oid = '" + oid
				+ "' and u.username<>'sys'";
		Session session = this.getSession();
		try {
			List<Usertable> temp = session.createQuery(hql).list();
			if (temp != null && temp.size() > 0) {
				for (int i = 0; i < temp.size(); i++) {
					Usertable usertable = temp.get(i);
					roleTemp.add(usertable);
				}
			}
		} catch (RuntimeException re) {
			logger.error("getUserRole failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
		return roleTemp;
	}

	public List<Usertable> getUserByName(String rname, String oid) {
		List<Usertable> roleTemp = new ArrayList();
		String hql = "from Usertable u,RoleUser ru,Role r where ru.uid=u.id and r.id=ru.rid and r.name='"
				+ rname + "' and u.username<>'sys' ";
		if (StringUtils.isNotBlank(oid)) {
			hql += "and u.oid = '" + oid + "'";
		}
		Session session = this.getSession();
		try {
			List<Object[]> temp = session.createQuery(hql).list();
			if (temp != null && temp.size() > 0) {
				for (int i = 0; i < temp.size(); i++) {
					Object[] obj = temp.get(i);
					Usertable role = (Usertable) obj[0];
					roleTemp.add(role);
				}
			}
		} catch (RuntimeException re) {
			logger.error("getUserRole failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
		return roleTemp;
	}

	public List<Usertable> getOrgUserserById(String uid) {
		List<Usertable> roleTemp = new ArrayList();
		String hql = "from Usertable a,Usertable b where a.id='" + uid
				+ "' and a.oid=b.oid and b.id<>'" + uid
				+ "' and u.username<>'sys' ";
		Session session = this.getSession();
		try {
			List<Object[]> temp = session.createQuery(hql).list();
			if (temp != null && temp.size() > 0) {
				for (int i = 0; i < temp.size(); i++) {
					Object[] obj = temp.get(i);
					Usertable role = (Usertable) obj[0];
					roleTemp.add(role);
				}
			}
		} catch (RuntimeException re) {
			logger.error("getUserRole failed");
			throw re;
		} finally {
			if (session != null) {
				releaseSession(session);
			}
		}
		return roleTemp;
	}

	/**
	 * 查询条件的封装
	 * 
	 * @param usertableForm
	 * @return
	 */
	private StringBuffer getStringBuffer(UsertableForm usertableForm,
			StringBuffer buf) {
		/*
		 * 需要加入查询条件时封装
		 */

		if (usertableForm.getTemp() != null
				&& usertableForm.getTemp().equals("login")) {
			buf.append(" where 1=1");
		} else {
			buf.append(" where (t.username<>'sys' or t.username is null) ");
		}
		if (StringUtils.isNotBlank(usertableForm.getId())) {
			buf.append(" and t.id = '" + usertableForm.getId().trim() + "'");
		} else {
			if (StringUtils.isNotBlank(usertableForm.getUsername())) {
				buf.append(" and t.username like '%"
						+ usertableForm.getUsername().trim() + "%'");
			}
			if (StringUtils.isNotBlank(usertableForm.getPassword())) {
				buf.append(" and t.password = '"
						+ usertableForm.getPassword().trim() + "'");
			}

			if (StringUtils.isNotBlank(usertableForm.getRname())) {
				buf.append(" and t.rname like '%"
						+ usertableForm.getRname().trim() + "%'");
			}
			if (StringUtils.isNotBlank(usertableForm.getOrg())) {
				buf.append(" and t.org like '%" + usertableForm.getOrg().trim()
						+ "%'");
			}
			if (StringUtils.isNotBlank(usertableForm.getOid())) {
				buf.append(" and t.oid = '" + usertableForm.getOid().trim()
						+ "'");
			}
			if (StringUtils.isNotBlank(usertableForm.getLevel())) {
				buf.append(" and t.level like '%"
						+ usertableForm.getLevel().trim() + "%'");
			}
			if (StringUtils.isNotBlank(usertableForm.getEducation())) {
				buf.append(" and t.education like '%"
						+ usertableForm.getEducation().trim() + "%'");
			}
			if (StringUtils.isNotBlank(usertableForm.getSpecialty())) {
				buf.append(" and t.specialty like '%"
						+ usertableForm.getSpecialty().trim() + "%'");
			}
			if (StringUtils.isNotBlank(usertableForm.getIds())) {
				buf.append(" and t.id not in (" + usertableForm.getIds().trim()
						+ ")");
			}
		}
		return buf;
	}
}
